package com.monlyu.bo;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Date;
import java.util.List;

import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.hibernate.Criteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import com.google.common.collect.Lists;
import com.monlyu.bean.Page;
import com.monlyu.bean.Pages;
import com.monlyu.exception.MsgException;
import com.monlyu.util.ArrayUtil;
import com.monlyu.util.DateUtil;
import com.monlyu.util.ExcelUtil;
import com.monlyu.util.LogUtil;
import com.monlyu.util.StringUtil;
import com.monlyu.util.WebOptUtil;
import com.monlyu.vo.Aduitor2User;
import com.monlyu.vo.Enums;
import com.monlyu.vo.Out2User;
import com.monlyu.vo.OuterApp;
import com.monlyu.vo.User;

/*******************************************************************************
 * 
 * <b>功能描述☞</b><br>
 * 
 * <b>开发公司☞</b>重庆唯尼科技有限公司<br>
 * <b>版权☞</b>本文件归重庆唯尼科技有限公司所有<br>
 * <b>项目名☞</b>NewsSys<br>
 * <b>文件路径☞</b>com.monlyu.bo.OuterBo.java<br>
 * <b>开发日期☞</b>2010-2-12 <br>
 * 
 * @author monlyu
 * @since JDK 5
 * @version 1.0.8
 * 
 ******************************************************************************/
@Repository(value = "outerBo")
public class OuterBo extends BaseBo {
	private UserBo userBo;
	private AduitorBo aduitorBo;
	private EnumsBo enumsBo;
	private DeviceuseBo deviceuseBo;

	public void setDeviceuseBo(DeviceuseBo deviceuseBo) {
		this.deviceuseBo = deviceuseBo;
	}

	public void setEnumsBo(EnumsBo enumsBo) {
		this.enumsBo = enumsBo;
	}

	public void setUserBo(UserBo userBo) {
		this.userBo = userBo;
	}

	/**
	 * 保存对象
	 * 
	 * @throws MsgException
	 *             异常信息
	 * 
	 * @since 2010-2-14
	 */
	public void save(OuterApp app, String user) throws MsgException {
		if (app.getId() == null) {
			app.setApper(curUser());
			app.setDate(DateUtil.now());
			app.setAduitor(null);
			dao.insertOrUpdate(app);
		}
		if (app.getId() != null) {
			deleteApp2User(app.getId());
			OuterApp dbApp=(OuterApp)dao.get(OuterApp.class, app.getId());
			dbApp.setStatus(app.getStatus());
			dbApp.setTvon(app.getTvon());
			dbApp.setMsgFrom(app.getMsgFrom());
			dbApp.setBegin(app.getBegin());
			dbApp.setEnd(app.getEnd());
			dbApp.setWhere(app.getWhere());
			dbApp.setGrants(app.getGrants());
			dbApp.setJoiner(app.getJoiner());
			dbApp.setTheme(app.getTheme());
			dbApp.setComment(app.getComment());
			dbApp.setDevice(app.getDevice());
			dao.insertOrUpdate(dbApp);
		}
		addUser(userBo.findUser(user), app);
	}

	private void addUser(List<User> usrs, OuterApp app) {
		for (User u : usrs) {
			//dao.load(u);
			dao.get(User.class, u.getId());
			Out2User er = new Out2User();
			er.setOuterApp(app);
			er.setUser(u);
			dao.insertOrUpdate(er);
		}
	}

	public void setAduitorBo(AduitorBo aduitorBo) {
		this.aduitorBo = aduitorBo;
	}

	public Pages<OuterApp> findOuts(Page page) {
		Criteria criteria = dao.initCriteria(OuterApp.class);
		criteria.add(Restrictions.eq("status", OuterApp.STATUS_AUDITING));
		criteria.add(Restrictions.in("tvon", getTvon()));
		return buildPages(page, criteria, "theme", "comment");
	}

	// 根据当前用户获取
	private List<Long> getTvon() {
		List<Long> mList = Lists.newArrayList();
		for (Aduitor2User au : aduitorBo.getPidByUser(curUser())) {
			mList.add(au.getPrograme());
		}
		if (mList.isEmpty()) {
			mList.add(-1l);
		}
		return mList;
	}

	/**
	 * 查询所有的信息
	 * 
	 * @since 2010-2-17
	 */
	public Pages<OuterApp> findMyOuts(Page defPage) {
		Criteria criteria = dao.initCriteria(OuterApp.class);
		criteria.add(Restrictions.eq("apper", curUser()));
		return buildPages(defPage, criteria, "theme", "comment");
	}

	/**
	 * 保存审核的意见
	 * 
	 * @since 2010-2-17
	 */
	public void saveAduit(OuterApp outerApp) {
		String hql = "update com.monlyu.vo.OuterApp a set a.aduitor=?,a.auditDate=?,a.aduitMsg=?,a.aduitDevMsg=?,a.grants=?,a.status=?,a.givedDevices=? where a.id=?";
		dao.execute(hql, curUser(),DateUtil.now(), outerApp.getAduitMsg(), outerApp
				.getAduitDevMsg(), outerApp.getGrants(), outerApp.getStatus(),
				outerApp.getGivedDevices(), outerApp.getId());
		deviceuseBo.delDevice(outerApp);
		//dao.load(outerApp);
		OuterApp dbOuterApp = dao.get(OuterApp.class, outerApp.getId());
		deviceuseBo.addDevice(dbOuterApp, ArrayUtil.str2Long(outerApp
				.getGivedDevices().split(",")));
	}

	/** 根据id获得用户,和设备的信息 */
	public Object[] getInfo(Long id) {
		OuterApp app = dao.get(OuterApp.class, id);
		StringBuilder devices = new StringBuilder();
		if (app != null && app.getDevice() != null) {
			List<Enums> val = enumsBo.getEnumsByIds(ArrayUtil.str2Long(
					app.getDevice().split(",")).toArray());
			for (Enums enums : val) {
				devices.append("<span class='split'>" + enums.getKey()
						+ "</span>");
			}
		}
		Object[] objects = new Object[4];
		List<Out2User> users = dao.find(Out2User.class, "outerApp.id", id);
		List<Long> uids = Lists.newArrayList();
		StringBuffer buffer = new StringBuffer();
		for (Out2User out2User : users) {
			uids.add(out2User.getUser().getId());
			buffer.append("<span class='split'>" + out2User.getUser().getName()
					+ "</span>");
		}

		StringBuilder devicesDetal = new StringBuilder();
		for (String devis : deviceuseBo.findDusedB(app.getId())) {
			devicesDetal.append("<span class='split'>" + devis + "</span>");
		}

		objects[0] = uids;
		objects[1] = buffer.toString();
		objects[2] = devices.toString();
		objects[3] = devicesDetal.toString();
		return objects;
	}

	@SuppressWarnings("unchecked")
	public Pages<OuterApp> find(Page defPage, final OuterApp outApps,
			final String user) {
		Criteria criteria = dao.initCriteria(OuterApp.class);
		buildFindCriteria(outApps, criteria);
		if (!StringUtil.isTrimEmpty(user)) {
			String hql = "select distinct(o.outerApp.id) from com.monlyu.vo.Out2User o where o.user.name like '%"
					+ user + "%'";
			criteria.add(Restrictions.in("id", (List<Long>) dao.find(hql)));
		}
		return buildPages(defPage, criteria);
	}

	// 提取出来的公用部分
	private void buildFindCriteria(OuterApp outApps, Criteria criteria) {
		if (outApps != null) {
			if (outApps.getTvon() != null && outApps.getTvon() > 0) {
				criteria.add(Restrictions.eq("tvon", outApps.getTvon()));
			}
			if (outApps.getMsgFrom() != null && outApps.getMsgFrom() > 0) {
				criteria.add(Restrictions.eq("msgFrom", outApps.getMsgFrom()));
			}
			if (outApps.getWhere() != null && outApps.getWhere() > 0) {
				criteria.add(Restrictions.eq("where", outApps.getWhere()));
			}
			if (outApps.getBegin() != null) {
				criteria.add(Restrictions.ge("begin", outApps.getBegin()));
			}
			if (outApps.getEnd() != null) {
				criteria.add(Restrictions.le("end", DateUtil.addDay(outApps
						.getEnd(), 1)));
			}
			if (!StringUtil.isTrimEmpty(outApps.getTheme())) {
				criteria.add(Restrictions.like("theme", outApps.getTheme(),
						MatchMode.ANYWHERE));
			}
			if (outApps.getStatus() > 0) {
				criteria.add(Restrictions.eq("status", outApps.getStatus()));
			}
		}
	}

	/**
	 * 列举草稿
	 */
	public Pages<OuterApp> listcg(Page defPage) {
		Criteria criteria = dao.initCriteria(OuterApp.class);
		criteria.add(Restrictions.eq("apper", curUser()));
		criteria.add(Restrictions.eq("status", OuterApp.STATUS_DRAFT));
		return buildPages(defPage, criteria, "theme", "comment");
	}

	/**
	 * 生成Excel信息
	 */
	@SuppressWarnings("unchecked")
	public InputStream find(OuterApp outerApp, String user) {
		Criteria criteria = dao.initCriteria(OuterApp.class);
		buildFindCriteria(outerApp, criteria);
		if (!StringUtil.isTrimEmpty(user)) {
			String hql = "select distinct(o.outerApp.id) from com.monlyu.vo.Out2User o where o.user.name like '%"
					+ user + "%'";
			criteria.add(Restrictions.in("id", (List<Long>) dao.find(hql)));
		}
		InputStream stream = null;
		try {
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			String[] head = { "栏目名称", "采访主题", "开始时间", "结束时间", "交通补助金额", "参与人员",
					"消息来源", "新闻属地", "新闻来源", "备注" };
			WritableWorkbook workbook = ExcelUtil.ininWorkbook(os);
			WritableSheet sheet = ExcelUtil.createSheet(workbook, "查询结果");
			ExcelUtil.addRow(sheet, 0, 0, head);
			List<OuterApp> apps = criteria.list();
			for (int i = 0; i < apps.size(); i++) {
				OuterApp app = apps.get(i);
				int row = i + 1;
				sheet.addCell(ExcelUtil.cell(0, row, enumsBo.getEnumValue(
						app.getTvon()).toString()));
				sheet.addCell(ExcelUtil.cell(1, row, app.getTheme()));
				sheet.addCell(ExcelUtil.cell(2, row, DateUtil.format(app
						.getBegin())));
				sheet.addCell(ExcelUtil.cell(3, row, DateUtil.format(app
						.getEnd())));
				sheet.addCell(ExcelUtil.cell(4, row,
						app.getGrants() == null ? "" : Integer.toString(app
								.getGrants().intValue())));
				sheet.addCell(ExcelUtil.cell(5, row, findUser(app.getId())));
				sheet.addCell(ExcelUtil.cell(6, row, enumsBo.getEnumValue(
						app.getMsgFrom()).toString()));
				sheet.addCell(ExcelUtil.cell(7, row, enumsBo.getEnumValue(
						app.getWhere()).toString()));
				sheet.addCell(ExcelUtil.cell(8, row, app.getComment()));
			}
			ExcelUtil.save(workbook);
			stream = new ByteArrayInputStream(os.toByteArray());
		} catch (Exception e) {
			LogUtil.log(getClass(), e);
		}
		return stream;
	}

	/**
	 * 导出外出费用
	 * 
	 * @param outerApp
	 * @param user
	 * @return
	 * @throws IOException
	 * @since chendan 2010-4-3
	 */
	@SuppressWarnings("unchecked")
	public InputStream export(OuterApp outerApp, String user)
			throws IOException {
		Criteria criteria = dao.initCriteria(OuterApp.class);
		Date beginDate = outerApp.getBegin();
		Date endDate = outerApp.getEnd();
		buildFindCriteria(outerApp, criteria);
		if (!StringUtil.isTrimEmpty(user)) {
			String hql = "select distinct(o.outerApp.id) from com.monlyu.vo.Out2User o where o.user.name like '%"
					+ user + "%'";
			criteria.add(Restrictions.in("id", (List<Long>) dao.find(hql)));
		}
		List<OuterApp> apps = criteria.list();
		String url = WebOptUtil.APP_ARGS.getWebFileDir()
				+ "common/report/outFee.xls";
		InputStream is = new FileInputStream(url);
		HSSFWorkbook wb = new HSSFWorkbook(is);
		is.close();
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFCellStyle style = wb.createCellStyle();
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		HSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 11);
		style.setFont(font);
		style.setWrapText(true);
		int rowNum = 0;
		short celNum = 0;
		HSSFRow row = sheet.getRow(rowNum++);
		// 下面开始组装数据
		HSSFCell cell = row.getCell(celNum);
		cell.setCellValue(new HSSFRichTextString("《"
				+ enumsBo.getEnumValue(outerApp.getTvon()) + "》外出费用统计表"));
		// 第二行
		row = sheet.getRow(rowNum++);
		// cell = row.getCell((short) 2);
		cell = row.createCell((short) 2, HSSFCell.CELL_TYPE_STRING);
		cell.setCellStyle(style);
		cell.setCellValue(new HSSFRichTextString(DateUtil.format(beginDate,
				"yyyy-MM-dd")));
		cell = row.getCell((short) 4);
		cell.setCellValue(new HSSFRichTextString(DateUtil.format(endDate,
				"yyyy-MM-dd")));
		rowNum++;
		for (int i = 0; i < apps.size(); i++) {
			OuterApp app = apps.get(i);
			celNum = 0;
			row = sheet.getRow(rowNum++);
			cell = row.createCell(celNum++, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(app.getTheme())); // 采访主题
			cell.setCellStyle(style);
			cell = row.createCell(celNum++, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(enumsBo.getEnumValue(
					app.getTvon()).toString())); // 栏目
			cell.setCellStyle(style);
			cell = row.createCell(celNum++, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(DateUtil.format(app
					.getBegin()))); // 申请时间
			cell.setCellStyle(style);
			cell = row.createCell(celNum++, HSSFCell.CELL_TYPE_NUMERIC);
			cell.setCellValue(app.getGrants().doubleValue()); // 车费补助
			cell.setCellStyle(style);
			cell = row.createCell(celNum++, HSSFCell.CELL_TYPE_STRING);
			cell.setCellValue(new HSSFRichTextString(
					app.getApper() == null ? "" : app.getApper().getName())); // 申请人
			cell.setCellStyle(style);
		}
		row = sheet.getRow(rowNum);
		cell = row.getCell((short) 3);
		cell.setCellFormula("sum(D4:D" + rowNum + ")");
		cell.setCellStyle(style);
		cell = row.getCell((short) 1);
		cell.setCellStyle(style);
		cell = row.getCell((short) 2);
		cell.setCellStyle(style);
		cell = row.getCell((short) 4);
		cell.setCellStyle(style);
		cell = row.getCell((short) 0);
		cell.setCellValue(new HSSFRichTextString("合计"));
		// 设置合并单元格
		sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 2));
		HSSFCellStyle style1 = wb.createCellStyle();
		style1.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style1.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style1.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style1.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style1.setFont(font);
		style1.setWrapText(true);
		style1.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
		cell.setCellStyle(style1);

		ByteArrayOutputStream os = new ByteArrayOutputStream();
		wb.write(os);
		return new ByteArrayInputStream(os.toByteArray());
	}

	/**
	 * 获取所有的用户列表
	 */
	private String findUser(Long appid) {
		StringBuilder builder = new StringBuilder();
		for (Out2User out2User : dao.find(Out2User.class, "outerApp.id", appid)) {
			builder.append(out2User.getUser().getName() + " ");
		}
		return builder.toString();
	}

	/* 更具Id删除所有的App2User */
	public void deleteApp2User(Long id) {
		dao.delete(dao.find(Out2User.class, "outerApp.id", id));
	}

	/** 指派外出 */
	public void savezhipai(OuterApp outerApp) {
		if (outerApp.getApper() != null && outerApp.getApper().getId() == null) {
			outerApp.setApper(null);
		} else {
			outerApp.setApper(dao.get(User.class, outerApp.getApper().getId()));
		}
		// outerApp.setAduitor(curUser());
		outerApp.setDate(DateUtil.now());
		outerApp.setAuditDate(DateUtil.now());
		//add by chenDan 20110722 计划任务无审核的情况下，需要指定默认审核人为指派人员
		if (outerApp.getStatus() == OuterApp.STATUS_DONE) {
			outerApp.setAduitor(curUser());
		}
		dao.insertOrUpdate(outerApp);
		addUser(userBo.findUser(outerApp.getJoiner()), outerApp);
		if (outerApp.getStatus() == OuterApp.STATUS_DONE) {
			deviceuseBo.delDevice(outerApp);
			deviceuseBo.addDevice(outerApp, ArrayUtil.str2Long(outerApp
					.getGivedDevices().split(",")));
		}
	}

	@SuppressWarnings("unchecked")
	public List<OuterApp> getFee(OuterApp outerApp) {
		Criteria criteria = dao.initCriteria(OuterApp.class);
		criteria.add(Restrictions.eq("status", OuterApp.STATUS_DONE));
		if (outerApp.getTvon()!=null && !outerApp.getTvon().equals(0)) {
			criteria.add(Restrictions.eq("tvon", outerApp.getTvon()));
		}
		if (outerApp.getBegin() != null) {
			criteria.add(Restrictions.ge("date", outerApp.getBegin()));
		}
		if (outerApp.getEnd() != null) {
			criteria.add(Restrictions.le("date", DateUtil.addDay(outerApp
					.getEnd(), 1)));
		}
		return criteria.list();
	}

	public InputStream getFeexls(OuterApp outerApp) {
		List<OuterApp> apps = getFee(outerApp);
		InputStream stream = null;
		try {
			ByteArrayOutputStream os = new ByteArrayOutputStream();
			String[] head = { "栏目名称", "采访主题", "开始时间", "结束时间", "交通补助金额", "参与人员",
					"消息来源", "新闻属地", "新闻来源", "备注" };
			WritableWorkbook workbook = ExcelUtil.ininWorkbook(os);
			WritableSheet sheet = ExcelUtil.createSheet(workbook, "查询结果");
			ExcelUtil.addRow(sheet, 0, 0, head);
			for (int i = 0; i < apps.size(); i++) {
				OuterApp app = apps.get(i);
				int row = i + 1;
				sheet.addCell(ExcelUtil.cell(0, row, enumsBo.getEnumValue(
						app.getTvon()).toString()));
				sheet.addCell(ExcelUtil.cell(1, row, app.getTheme()));
				sheet.addCell(ExcelUtil.cell(2, row, DateUtil.format(app
						.getBegin())));
				sheet.addCell(ExcelUtil.cell(3, row, DateUtil.format(app
						.getEnd())));
				sheet.addCell(ExcelUtil.cell(4, row,
						app.getGrants() == null ? "0" : Integer.toString(app
								.getGrants().intValue())));
				sheet.addCell(ExcelUtil.cell(5, row, findUser(app.getId())));
				sheet.addCell(ExcelUtil.cell(6, row, enumsBo.getEnumValue(
						app.getMsgFrom()).toString()));
				sheet.addCell(ExcelUtil.cell(7, row, enumsBo.getEnumValue(
						app.getWhere()).toString()));
				sheet.addCell(ExcelUtil.cell(8, row, app.getComment()));
			}
			ExcelUtil.save(workbook);
			stream = new ByteArrayInputStream(os.toByteArray());
		} catch (Exception e) {
			LogUtil.log(getClass(), e);
		}
		return stream;
	}

	public DeviceuseBo getDeviceuseBo() {
		return deviceuseBo;
	}

	public Pages<OuterApp> listjh(Page defPage) {
		Criteria criteria = dao.initCriteria(OuterApp.class);
		// criteria.add(Restrictions.eq("aduitor.id", curUser().getId()));
		criteria.add(Restrictions.in("tvon", getTvon()));
		criteria.add(Restrictions.eq("status", OuterApp.STATUS_ZHIPAI_TMP));
		return buildPages(defPage, criteria, "theme", "comment");
	}
}
